﻿using LY.Entity;
using LY.Entity.PageSearch;
using LY.Entity.SearchEntity;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LY.DataAccess.Repository
{
    public class AboutRepository : BaseRepository
    {
        /// <summary>
        /// 根据code查找指定栏目名称
        /// </summary>
        /// <param name="search"></param>
        /// <param name="view"></param>
        /// <returns></returns>
        public List<C_Menu> GetMenu(string code)
        {
            string sql = @" SELECT [ID]
      ,[Name] 
      ,[Code]
      ,[UpperID]
      ,[Sequence]
      FROM [C_Menu]
      WHERE ID=(SELECT ID FROM [C_Menu] WHERE Code=@Code) order by [Sequence]";
            return base.Query<C_Menu>(sql, new { Code = code });
        }
        /// <summary>
        /// 根据栏目Id检索文章信息,返回单个实体对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public C_Article GetArticleMenuId(int? id)
        {
            string sql = @" SELECT [ID]
                                  ,[Name]
                                  ,[Keywords]
                                  ,[Author]
                                  ,[Synopsis]
                                  ,[Content]
                                  ,[TxtContent]
                                  ,[Hits]
                                  ,[CreateTime]
                                  ,[CreateUser]
                                  ,[CreateUserID]
                                  ,[PublishTime]
                                  ,[PublishUser]
                                  ,[PublishUserID]
                                  ,[UpdateTime]
                                  ,[IsTop]
                                  ,[Source]
                                  ,[Status]
                                  ,[Sequence]
                                  ,[URL]
                                  ,[MediaName]
                                  ,[MediaURL]
                                  ,[Pictures]
                                  ,[PicturesPath]
                                  ,[Files]
                                  ,[FilesPath]
                                  ,[Flashs]
                                  ,[FlashsPath]
                                  ,[TitlePic]
                                  ,[Vedios]
                                  ,[VedioPath]
                                  ,[Menu]
                                  ,[MenuId]
                    from [C_Article]   where [PublishTime] in(select MAX(PublishTime) from [C_Article] where [MenuId]=@ID) and [MenuId]=@ID";
            return base.Get<C_Article>(sql, new { ID = id });
        }
        /// <summary>
        /// 根据文章Id检索文章信息,返回单个实体对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public C_Article GetArticleById(int? id)
        {
            string sql = @" SELECT [ID]
                                  ,[Name]
                                  ,[Author]
                                  ,[Content]
                                  ,[CreateTime]
                                  ,[CreateUser]
                    from [C_Article]   where  [ID]=@ID";
            return base.Get<C_Article>(sql, new { ID = id });
        }
        /// <summary>
        /// 活动专区中的分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public List<C_Article> GetArticleListPage(int? pageIndex, int pageSize)
        {
            StringBuilder sb = new StringBuilder();
            if (pageSize == 0)
            {
                sb.Append(@" SELECT [ID]
                                  ,[Name]
                                  ,[Keywords]
                                  ,[Author]
                                  ,[Synopsis]
                                  ,[Content]
                                  ,[TxtContent]
                                  ,[Hits]
                                  ,[CreateTime]
                                  ,[CreateUser]
                                  ,[CreateUserID]
                                  ,[PublishTime]
                                  ,[PublishUser]
                                  ,[PublishUserID]
                                  ,[UpdateTime]
                                  ,[IsTop]
                                  ,[Source]
                                  ,[Status]
                                  ,[Sequence]
                                  ,[URL]
                                  ,[MediaName]
                                  ,[MediaURL]
                                  ,[Pictures]
                                  ,[TitlePic]
                                  ,[PicturesPath]
                                  ,[Files]
                                  ,[FilesPath]
                                  ,[Flashs]
                                  ,[FlashsPath]
                                  ,[Vedios]
                                  ,[VedioPath]
                                  ,[Menu]
                                  ,[MenuId]
                                    FROM [C_Article] WHERE 1=1 ");
            }
            else if (pageIndex==null)
            {
                sb.Append("SELECT TOP " + pageSize + @" [ID]
                                  ,SUBSTRING([Name],0,30) as [Name]
                                  ,[Keywords]
                                  ,[Author]
                                  ,[Synopsis]
                                  ,[Content]
                                  ,[TxtContent]
                                  ,[Hits]
                                  ,[CreateTime]
                                  ,[CreateUser]
                                  ,[CreateUserID]
                                  ,[PublishTime]
                                  ,[PublishUser]
                                  ,[PublishUserID]
                                  ,[UpdateTime]
                                  ,[IsTop]
                                  ,[Source]
                                  ,[Status]
                                  ,[Sequence]
                                  ,[URL]
                                  ,[MediaName]
                                  ,[MediaURL]                                           
                                  ,[Pictures]
                                  ,[TitlePic]
                                  ,[PicturesPath]
                                  ,[Files]
                                  ,[FilesPath]
                                  ,[Flashs]
                                  ,[FlashsPath]
                                  ,[Vedios]
                                  ,[VedioPath]
                                  ,[Menu]
                                  ,[MenuId]
                                    FROM C_Article WHERE 1=1 ORDER BY PublishTime DESC ");
            }else if (pageIndex >= 1)
                {
                    sb.Append("SELECT TOP " + pageSize + @" [ID]
                                  ,SUBSTRING([Name],0,30) as [Name]
                                  ,[Keywords]
                                  ,[Author]
                                  ,[Synopsis]
                                  ,[Content]
                                  ,[TxtContent]
                                  ,[Hits]
                                  ,[CreateTime]
                                  ,[CreateUser]
                                  ,[CreateUserID]
                                  ,[PublishTime]
                                  ,[PublishUser]
                                  ,[PublishUserID]
                                  ,[UpdateTime]
                                  ,[IsTop]
                                  ,[Source]
                                  ,[Status]
                                  ,[Sequence]
                                  ,[URL]
                                  ,[MediaName]
                                  ,[MediaURL]
                                  ,[Pictures]
                                  ,[TitlePic]
                                  ,[PicturesPath]
                                  ,[Files]
                                  ,[FilesPath]
                                  ,[Flashs]
                                  ,[FlashsPath]
                                  ,[Vedios]
                                  ,[VedioPath]
                                  ,[Menu]
                                  ,[MenuId]
                                    FROM [C_Article] WHERE 1=1 AND [ID] NOT IN(SELECT TOP " + (pageIndex - 1) * pageSize + " [ID] FROM [C_Article] ORDER BY PublishTime DESC) ORDER BY PublishTime DESC ");
                    //sb.Append("AND [ID] NOT IN(SELECT TOP " + (pageIndex - 1) * pageSize + " [ID] FROM [C_Article] ORDER BY PublishTime DESC) ORDER BY PublishTime DESC");
                    //sb.Append(" AND [ID] > (SELECT [ID] FROM WHERE ID NOT IN(SELECT TOP " + (pageIndex - 1) * pageSize + " [ID] FROM [C_Article] WHERE [ID]>0 ");
                    //sb.Append(" ORDER BY [ID] DESC)a) ");
            }
            return base.Query<C_Article>(sb.ToString(), null);
        }
        /// <summary>
        /// 根据文章ID查找文章信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public C_Article GetArticle(int? id)
        {
            string sql = @"SELECT [ID]
                                  ,[Name]
                                  ,[Keywords]
                                  ,[Author]
                                  ,[Synopsis]
                                  ,[Content]
                                  ,[TxtContent]
                                  ,[Hits]
                                  ,[CreateTime]
                                  ,[CreateUser]
                                  ,[CreateUserID]
                                  ,[PublishTime]
                                  ,[PublishUser]
                                  ,[PublishUserID]
                                  ,[UpdateTime]
                                  ,[IsTop]
                                  ,[Source]
                                  ,[Status]
                                  ,[Sequence]
                                  ,[URL]
                                  ,[MediaName]
                                  ,[MediaURL]
                                  ,[Pictures]
                                  ,[PicturesPath]
                                  ,[Files]
                                  ,[FilesPath]
                                  ,[Flashs]
                                  ,[FlashsPath]
                                  ,[TitlePic]
                                  ,[Vedios]
                                  ,[VedioPath]
                                  ,[Menu]
                                  ,[MenuId]
                                  FROM [C_Article] WHERE ID=@ID";
            return base.Get<C_Article>(sql, new { ID = id });
        }
    }
}
